Problem with Index

Printable View

12-16-2002, 07:04 PM
msuresh

Problem with Index

Hi all,
I have a table called log_record which have a column name tran_datetime.I created an index xx on this column in descending order and when i checked the dba_ind_columns,the column name is showign as SYS_NC00083$.Can anyone reply me what could be the problem??? since my query access this table in descending order.

Thanks
M.Suresh
12-16-2002, 07:19 PM
LKBrwn_DBA

No problem.
Oracle generates the SYS... for all 'descending' indexes, nut the index will be created correctly.

:rolleyes:
12-16-2002, 07:40 PM
msuresh

hi,
My query is select * from log_record order by tran_datetime desc;

This table is having millions of record.What to do now???
12-16-2002, 11:56 PM
slimdave

A couple of comments.

Firstly, you do not need to create the index as DESCENDING in order for it to be used to help with the sort order DESC of the query -- either an ascending or descending index could be used for this.

Secondly, is the tran_datetime column nullable? Without a NOT NULL constraint it could not be used to help in this query unless you included the predicate "tran_datetime IS NOT NULL".
12-17-2002, 12:31 PM
msuresh

Yes this is a nullable column.Is there any way i get good performance on this query since the application use this a primary query.

Thanks
12-17-2002, 01:26 PM
slimdave

If you can't put a NOT NULL constraint on there, then what might work is a function-based index .

You could create an index like ...

create index my_index on log_record (NVL(TRAN_DATETIME,TO_DATE('01-JAN-3000','DD-MON-YYYY')));

Then the clause would be ...

ORDER BY NVL(TRAN_DATETIME,TO_DATE('01-JAN-3000','DD-MON-YYYY'))

... and you would adjust the '01-JAN-3000' date according to whether you want the nulls at the end or the beginning.
12-18-2002, 04:35 AM
julian

Quote:

Originally posted by slimdave

Secondly, is the tran_datetime column nullable? Without a NOT NULL constraint it could not be used to help in this query unless you included the predicate "tran_datetime IS NOT NULL".

Wrong. How do you know if the optimizer will not use it. Look at what I did:

Code:

SQL> desc file_size Name Null? Type ------------------------------- -------- ---------------------- SID NOT NULL NUMBER PVM NOT NULL DATE DB_NAME VARCHAR2(15) FILE_NAME VARCHAR2(100) SIZE_MB VARCHAR2(20) USED_MB VARCHAR2(20) FREE_MB VARCHAR2(20) HWM_MB VARCHAR2(20) -- Note DB_NAME is nullable SQL> CREATE INDEX ndx_db_name 2 ON file_size 3 ( db_name ); Index created. SQL> analyze table file_size compute statistics; Table analyzed. SQL> set autotrace on SQL> select * from file_size where db_name = 'SS02_V60'; SID PVM DB_NAME FILE_NAME SIZE_MB USED_MB FREE_MB HWM_MB ---------- --------------- --------------- --------------------------------------------- -------------------- -------- 1 17-DEC-02 SS02_V60 /u03/oradata1/ss02_v60/story_data01.dbf 2048 700 1077 970 . . . 12 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=12 Bytes=816) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'FILE_SIZE' (Cost=3 Card=12 Bytes=816) 2 1 INDEX (RANGE SCAN) OF 'NDX_DB_NAME' (NON-UNIQUE) (Cost=1 Card=12) Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 9 consistent gets 0 physical reads 0 redo size 1397 bytes sent via SQL*Net to client 340 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 12 rows processed -- See, Oracle did use the index! SQL> SQL> SQL> SQL>
12-18-2002, 08:01 AM
kiewer

Quote:

Originally posted by msuresh
hi,
My query is select * from log_record order by tran_datetime desc;

This table is having millions of record.What to do now???

there is no options to speed up this query with indexes. The only thing, what you should do - to tune sort operations in your database.
12-18-2002, 08:15 AM
pando

is that 8i or 9i Julian?
12-18-2002, 09:07 AM
DaPi

Quote:

Originally posted by msuresh
hi,
My query is select * from log_record order by tran_datetime desc;

This table is having millions of record.What to do now???

Weeeell - what are you going to do with them all?
- If YOU are going to read them all (talk to you again in 2015) then that's it, it will take a time, you're probably processing around a Gig.
- If a machine is going to read them all, are you sure you need them sorted at this point? Can the sort be postponed until the output is presented for human consumption? (An unsorted list would use a FULL SCAN which would be faster.)

It might be worth experimenting WITHOUT an index, FULL SCAN plus sorts would be used, the optimiser does not ALWAYS get it right (but it usually does better than I can!).
12-18-2002, 09:49 AM
slimdave

Julian,

Read the question again -- it's about using an index for ORDER BY, not for WHERE. I stand by by comment on the NOT NULL contraint.

Kiewer

Oracle can choose to use an index (with certain limitations, particularly to do with character types and NLS) to perform an ORDER BY. It can negate the need for a sort operation

The index scan is definatly going to help on ordering many millions of rows.
12-18-2002, 10:45 AM
julian

You are right. The question *is* about ORDER BY. Then it is really up to if the column is nullable or not. I did test that.
12-18-2002, 10:48 AM
julian

Quote:

Originally posted by pando
is that 8i or 9i Julian?

9.2.0.2.0

But the guy is right. With only order by (no where clause) the optimizer goes for FTS although I have an index. But when the column is a NOT NULL one, then it goes for the IFS.
12-18-2002, 11:11 AM
slimdave

I suppose this restriction would be lifted if the index were of BITMAP type, since they do store null values.
12-18-2002, 11:16 AM
kiewer

Quote:

Originally posted by slimdave
Kiewer

Oracle can choose to use an index (with certain limitations, particularly to do with character types and NLS) to perform an ORDER BY. It can negate the need for a sort operation

The index scan is definatly going to help on ordering many millions of rows.

2 slimdave

I thought we were speaking about specific query

Quote:

select * from log_record order by tran_datetime desc;

But nevertheless I can't imagine how does index help to perform "order by". Please, give me an example.

Regarding to sort, index could be helpfull for count(*) queries with counting on unique column or for "group by" queries if unique index exists on group of columns which are included in "group by". But not more.

2 Julian

Quote:

But when the column is a NOT NULL one, then it goes for the IFS.

How did you get it for this type of query? I mean the query above.
12-18-2002, 11:32 AM
slimdave

A BTree index is basically just a sorted list of values for one or more columns, with a pointer to rowid's in the table, right?

So if you wanted to "select my_column from my_table order by my_column", Oracle can just scan an index on my_column from start to finish (for an ascending index) and return the result, no sorting required.

For "select my_column from my_table order by my_column DESC", Oracle would reverse the direction of the scan.

Now if you wanted "select my_column, other_column from my_table order by my_column, other_column" and you have a composite index on (my_column,other_column) the same applies. If your index was on (other_column,my_column), Oracle could scan the index and then sort the result, but still wouldn't touch the table.

Now suppose that you try "select my_column, other_column from my_table order by my_column" but other_column is not indexed. Oracle could still scan the index on my_column and access the table based on the order of rowid's retrieved from the index. No sort would be required here either.

All of this applies only if every value requested in the query is in the index. For a btree index the NULL values of my_column would not be present, so if there is no NOT NULL constraint on my_column Oracle will assume that there are null values in the table, and that the ORDER BY cannot be satisfied on the index alone. Therefore a full table scan and a sort would be required. A bitmap index would contain any NULL values, so it could be used to satisfy the ORDER BY.

My comment on character date types and NLS referred to the NLS_SORT parameter. Since indexes store values in binary order, not linguistic order, a linguistic sort (AaBbCcDd ...) cannot be performed just by scanning the index, which would give you (ABCD ... abcd ...). You can overcome this by setting NLS_SORT=BINARY.
12-18-2002, 12:03 PM
kiewer

2 slimdave
2 julian

Yes, you are both right. Sorry.

For me it just still a question whether "FTS+sort" always more expensive then "IFS+access by rowid" for querieing more then only indexed columns (of sure, supposed no "where" clause).
12-18-2002, 12:08 PM
slimdave

That's a difficult question to answer for sure, but I'd bet that the index access is faster in 99.9% of cases. The comparative efficiency would depend on many factors, but I would expect that the benefits of the index method would increase as the number of rows/blocks increases, and as the clustering factor of the index decreases.
12-18-2002, 12:45 PM
kiewer

2 slimdave

Ok. I can give you 0.1% of cases :) I mean that "gets" of 1289 block alwase takes longer then "gets" of 35 blocks. Sure, it also depends on sort_area_size which I have big enough for doing everything in memory.

PHP Code:

SQL> create table t as select * from all_objects; Tabelle wurde angelegt. SQL> set autot trace SQL> select * from t order by object_id; 1532 Zeilen ausgewõhlt. Ausf³hrungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 TABLE ACCESS (FULL) OF 'T' Statistiken ---------------------------------------------------------- 0 recursive calls 12 db block gets 23 consistent gets 19 physical reads 0 redo size 113511 bytes sent via SQL*Net to client 11743 bytes received via SQL*Net from client 104 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1532 rows processed SQL> create unique index it on t(object_id); Index wurde angelegt. SQL> set autot off SQL> analyze table t compute statistics; Tabelle wurde analysiert. SQL> analyze index it compute statistics; Index wurde analysiert. SQL> alter session set optimizer_index_cost_adj = 1; Sitzung wurde geõndert. SQL> set autot trace SQL> select * from t order by object_id; 1532 Zeilen ausgewõhlt. Ausf³hrungsplan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1532 Bytes=1 31752) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=12 Card=1532 By tes=131752) 2 1 INDEX (FULL SCAN) OF 'IT' (UNIQUE) (Cost=4 Card=1532) Statistiken ---------------------------------------------------------- 5 recursive calls 0 db block gets 1289 consistent gets 0 physical reads 0 redo size 113511 bytes sent via SQL*Net to client 11743 bytes received via SQL*Net from client 104 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1532 rows processed SQL>
12-18-2002, 01:24 PM
slimdave

I'll agree that the block gets take more work, but the payoff comes from eliminating a large (potentially disk-based) sort, and that should compensate for the gets.

Could you run timings for your queries -- I see similar patterns in the gets for your example, but faster execution time on the index access.
12-19-2002, 07:05 AM
DaPi

Quote:

Originally posted by kiewer
For me it just still a question whether "FTS+sort" always more expensive then "IFS+access by rowid"

I seems to me that, for reading the WHOLE table, unless you have a VAST amount of memory (i.e. can sort the whole table in RAM, in which case "FTS+sort" should win):

- The amount of disk i/o in "FTS+sort" will increase as N*log(N), BUT these are sequential i/o (faster than random access).

- The amount of disk i/o in "IFS+rowid" will increase as N, BUT these are random access i/o (slower than sequential).

The question is "where is the cross-over?" The log(N) increase will, in practice, be determined by the ratio of the file size to the sort-area. So the more memory you have (with good tuning) the better "FTS+sort" will work, until you get to a critical table size.
(I am assuming that the IFS time is negligible compared with the rest.)

The only answer is to try it and see.
12-19-2002, 07:50 AM
DaPi

Addendum:
In the general case, unless the whole table fits in the buffer, "IFS+rowid" would also have to read a lot of the physical blocks several times (worst case, once per row in the block for every block!)

In the case that started this discussion (sorting by timestamp) it is very likely that the data is "almost" sorted by physical location and sucessive reads by rowid would hit the same block.
12-19-2002, 08:36 AM
kiewer

Quote:

Originally posted by slimdave
Could you run timings for your queries -- I see similar patterns in the gets for your example, but faster execution time on the index access.

You are right again :-) I did the dirty test for tables with size 10000, 100000 and 1000000 rows and found approximatly the same or better performance for "IFL+rowid".

The conditions were:
1.Everything is cached in SGA (table and index, so 0% physical reads for "IFL+rowid")
2.The sorts are going to the disk, because I can't dedicate more then 100M sort_area_size on my test box and this isn't enough for sorting 1000000 rows (the sorting column - integer from 1 to 1000000).

Nevertheless that "IFL+rowid" needs 10 times more block reads than "FTS+sort", disk sorts degrade performance significally.
12-19-2002, 08:48 AM
pando

do you see sort improvement with 100mb sort_area_size? I have tested with 32mb and 64mb with not much differentce, seems that after passing some limit bigger sort_area_size doesnt imrpove performance
12-19-2002, 09:44 AM
DaPi

Quote:

Originally posted by kiewer
The conditions were:
1.Everything is cached in SGA (table and index, so 0% physical reads for "IFL+rowid")

I suspect that this mean the test is not applicable to the original question which had "millions of rows".
12-19-2002, 09:55 AM
kiewer

Quote:

Originally posted by pando
do you see sort improvement with 100mb sort_area_size? I have tested with 32mb and 64mb with not much differentce, seems that after passing some limit bigger sort_area_size doesnt imrpove performance

My concern was only to fit the sorts into memory (avoid disk sorts). I didn't compare memory sorts with different sort_area_size. IMHO Oracle has one stable algorithm for sorting and only checks whether it could be done in memory or must be splited into disk sorts. If it could be done in memory - so doesn't metter how much of it do you have. Just enough. But once again - this is just my IMHO.

The problem with big sort_area_size what I've expirienced is that optimizer prefers SORT JOIN rather than NESTED LOOPS, which are (SORT JOIN) much slow on my queries.
12-19-2002, 10:02 AM
kiewer

Quote:

Originally posted by DaPi
I suspect that this mean the test is not applicable to the original question which had "millions of rows".

Why not? I've tested with 1 million row table. Do you think it isn't enough?
12-19-2002, 10:06 AM
DaPi

:o OOPS SORRY - need new glasses, miscounted the 0's.
12-19-2002, 10:13 AM
julian

Very interesting thread...
12-19-2002, 12:04 PM
slimdave

Quote:

Originally posted by DaPi
In the case that started this discussion (sorting by timestamp) it is very likely that the data is "almost" sorted by physical location and sucessive reads by rowid would hit the same block.

Indeed, as long as there are not heavy deletes. In fact the data would not have to be sorted, there would just have to be a strong correlation between index blocks and table blocks. Oracle has access to that measurement through the clustering factor in dba_indexes or dba_ind_partitions.
12-19-2002, 12:14 PM
DaPi

Unfortunately he wants it backwards - DESC. So it does need sorting if you read it with FTS.
(again I would question NEED . . . . see my post on page one)
12-19-2002, 12:22 PM
slimdave

Desc, Asc, either way an FTS will require a sort -- I don't know that there is any saving in having the table physically sorted in the desired order. Might be worth testing.