How to create index?

Here is the details:-




columnA, columnB


Select * from tableA where columnA = columnB

I would like to create index for this where clause "columnA=columnB" in Oracle database, is it possible to do that?


Hope it help you ^^

Creating a Function-Based Index

Function-based indexes facilitate queries that qualify a value returned by a function or expression. The value of the function or expression is precomputed and stored in the index.

In addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked DETERMINISTIC. Also, you just have the EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.

Additionally, to use a function-based index:

The table must be analyzed after the index is created.

The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.

Note: CREATE INDEX stores the timestamp of the most recent function used in the function-based index. This timestamp is updated when the index is validated. When performing tablespace point-in-time recovery of a function-based index, if the timestamp on the most recent function used in the index is newer than the timestamp stored in the index, then the index is marked invalid. You must use the ANALYZE INDEX...VALIDATE STRUCTURE statement to validate this index. To illustrate a function-based index, consider the following statement that defines a function-based index (area_index) defined on the function area(geo):

CREATE INDEX area_index ON rivers (area(geo));

In the following SQL statement, when area(geo) is referenced in the WHERE clause, the optimizer considers using the index area_index.

SELECT id, geo, area(geo), desc
FROM rivers
WHERE Area(geo) >5000;

Table owners should have EXECUTE privileges on the functions used in function-based indexes.

Because a function-based index depends upon any function it is using, it can be invalidated when a function changes. If the function is valid, you can use an ALTER INDEX...ENABLE statement to enable a function-based index that has been disabled. The ALTER INDEX...DISABLE statement lets you disable the use of a function-based index. Consider doing this if you are working on the body of the function

Query to create index in Oracle:

CREATE INDEX idx ON test(id)
TABLESPACE idxs_indx;

idx is the name of the index
test is the table name
id is the column name
idxs_indx is the tablespace name

If you want to store all the indexes in a separate tablespace, then tablespace name is required. Else you can just use the query:

CREATE INDEX idx ON test(id);

To view the created indexes, you can use the following query:

select * from all_ind_column where table_name = 'test';

For your question, I believe the answer would be something like this:

create index idx on test(id) where columnA=columnB;

Let me know, if it works.

I think, probably the below links helps to achieve what you want
Does Oracle have a filtered index concept?

Category:sql Time:2018-07-09 Views:0

Related post

  • how to create index in two table mysql? 2011-01-13

    please help me how to create index between two tables in mysql? I have "account" and "group" table. i have to index with "group_id with index_id)" and to be unique. --------------Solutions------------- MySQL table indexes are on single tables. Perhap

  • How to create INDEX for large data in ORACLE(crores of data) 2011-05-05

    I want to create an Index for some table. I tried manually by writing index syntax, like create index index_name on table ......................... with this I tried, but it is taking somuch time for large data (like 15 crores) . But I heard that we

  • How to create indexes on MQT(materialized query table) in Db2? 2011-06-16

    How to create indexes on MQT(materialized query table) in Db2? I haven't found this information in documentation? Is index creation syntax the same as for common tables? --------------Solutions------------- After you create your MQT you have to refre

  • How to create index for my table? 2011-09-05

    I have a table with format below. And I also know the most common used sql on it, so my question is how to create index on my table thus this sql query can have best performance. Btw, my db is sybase ASE 12.5. Table t: bu, name, date, score_a, score_

  • How to create index for dynamic search strings 2010-01-23

    I have a little DB, for academic purpose only, and I have object tables at most. I've created a entity-relationship model (ERM) in Power Designer and the program, by default, creates index for the serial id's for each table. I want to know how do I u

  • How to create index faster? 2010-05-24

    I have 10mln rows in my table in MySQL and 7 indexes on this table. Now when I try to add 8th it takes infinite time to do this. Is there any way to workaround this problem to add easily and fast 8th index? --------------Solutions------------- This i

  • Sqlite - How to create indexes faster in 2011-07-24

    I have a table of about 60GB and I'm trying to create an index, and its very slow (almost a day, and still running!) I see most of the time is on Disk I/O(4MB/s), and it doesn't use the memory or cpu so much I tried: running 'pragma cache_zise = 1000

  • How to create index on massive data (mysql) 2011-10-07

    I am currently evaluating strategy for storing supplier catalogs. There can be multiple items in catalog vary from 100 to 0.25million. Each item may have multiple errors. application should support browsing of catalog items Group by Type of Error, Ca

  • PostgreSQL: How to create index on very large table without timeouts? 2013-09-17

    I am trying to add a simple index with the following SQL in Postgres, but the command keeps timing out: CREATE INDEX playlist_tracklinks_playlist_enid ON playlist_tracklinks (playlist_enid); The table definition is as follows: => \d playlist_track

  • PostgreSQL: SELECT INTO - how to create indexes? 2009-08-26

    since SELECT INTO NEW_TABLE FROM QUERY creates NEW_TABLE the new table will not have any indices. Is there some way to utilise SELECT INTO with an existing table where I've created the desired indices? I am aware of INSERT INTO TABLE SELECT ... but I

  • How to create index in SAP? 2009-11-28

    Currently we are interfacing our application with SAP. We are having a lot of performance problems with bapi/rfc approach. So recently we are trying "direct sql" approach to retrieve some data from SAP. Unfortunately, we've found that some of SAP tab

  • MongoDB, how to create index for query containing a geospatial query, a range query and sort on different columns? 2012-02-01

    So if I have a query that does the following (in pseudo code) find(a nearby x, b > y).sort(c) where a is a geo column, b is type of long, and c is also a type of long Would the compound index on (a:2d, b:1, c:1) work and suggested? --------------S

  • How to create index on column to increase execution of query 2012-04-15

    I will not add query here because there is nothing special in query. In query I have left join on 10 tables. ...left join tableB b on a.UserId = b.UserId left join tableC c on a.UserId = c.UserId left join tableD d on a.UserId = d.UserId Execution of

  • How to create indexes on multiple columns 2010-09-16

    We have the following entity relationships where a User belongs to a particular Organization. My queries either look like "select * from User where org=:org" or "select * from User where org=:org and type=:type" I have separate indexes on the User cl

  • How to create index for table having 30 columns where Select statement is generated dynamically 2011-02-11

    I have a table containing language translations. It has 30 columns for 30 languages. My requirement is to search from all columns for a particular word (say "hello"). I dynamically create Select statement: Select * from Languages where {English = "he

  • How to create indexs on a different file of Filegroup 2011-10-19

    i'm using sqlserver 2008 R2 is there any way to specify that a table index is created on a sencondary file from a filegroup? example: "Primay" Filegroup has 2 files: Catalog.mdf and Indexes.ndf how can i specify that my new index is created on the se

  • MongoDB and Rails: How to create Index 2012-01-07

    I was looking for a way in the initializer mongo_config.rb to create an index for locations. In other words, I want to be able to do{"gps" : "2d"}) in the initializer and in ruby. How do I do this? --------------Solutions---------

  • How-to create index on multiple documents with reducing 2012-04-13

    I've the following documents : public class User { public string Id { get; set; } public string Email { get; set; } public string Password { get; set; } } public class Book { public string Id { get; set; } public string Title { get; set; } } public c

  • Will indexing improve varchar(max) query performance, and how to create index 2012-05-02

    Firstly, I should point out I don't have much knowledge on SQL Server indexes. My situation is that I have an SQL Server 2008 database table that has a varchar(max) column usually filled with a lot of text. My ASP.NET web application has a search fac

Copyright (C), All Rights Reserved.

processed in 1.614 (s). 14 q(s)