Home » SQL & PL/SQL » SQL & PL/SQL » INDEX (Oracle 10G free download, Windows XP home edition)
INDEX [message #382846] Sun, 25 January 2009 18:04 Go to next message
amiravi
Messages: 3
Registered: January 2009
Junior Member
Hi,

I created a simple index on the Bills table (shown below) which was successful. However, select on the table does not show the table to be sorted according to the index. Can you please help me out with this?

Thanks in advance.

Regards,
Amit

1. Table Name: Bills
------------------
Name Amount Account_id
-------------------------------------------------
PHONE COMPANY 125 1
POWER COMPANY 75 1
RECORD CLUB 25 3
SOFTWARE COMPANY 250 5
CABLE TV COMPANY 35 2

2. CREATE INDEX BILLS_IDX ON BILLS( ACCOUNT_ID );
Successful

3. Select * from Bills;
shows the table in the same order. No changes.

[Updated on: Sun, 25 January 2009 18:07]

Report message to a moderator

Re: INDEX [message #382847 is a reply to message #382846] Sun, 25 January 2009 18:53 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
An index does not produce order for a SELECT, an ORDER BY produces order for a SELECT.
Re: INDEX [message #382852 is a reply to message #382847] Sun, 25 January 2009 21:22 Go to previous messageGo to next message
amiravi
Messages: 3
Registered: January 2009
Junior Member
Hi ebrian,

Thanks for replying. I understand Order by produces order for select. However, I believe if I create index for the table and then query the table, it will be shown in sorted order without the user of ORDER BY clause. Isn't this true?

Please do let me know.

Re: INDEX [message #382853 is a reply to message #382846] Sun, 25 January 2009 21:57 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
An index does not produce order for a SELECT, an ORDER BY produces order for a SELECT.

>I believe if I create index for the table and then query the table, it will be shown in sorted order without the user of ORDER BY clause.
NOT True!
You believe incorrectly.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you

[Updated on: Sun, 25 January 2009 22:03]

Report message to a moderator

Re: INDEX [message #382856 is a reply to message #382846] Sun, 25 January 2009 22:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The following is just to add a little more explanation to what has already been said. When the optimizer chooses an execution plan for retrieving your rows, it may or may not choose to use the index, even with an index hint. With so few rows in your table and no conditions that would cause using the index to be a more efficient method, it will almost undoubtedly do a full table scan, rather than use the index. The only way that Oracle guarantees a certain order is when you use an order by clause. The following shows first a full table scan on a small data set, then an index range scan when more data is added and a where condition is used that would return a small portion of that large dataset, so that it is more efficient to use the index.

SCOTT@orcl_11g> -- table, data, and index you provided:
SCOTT@orcl_11g> CREATE TABLE bills
  2    (Name	    VARCHAR2 (30),
  3  	Amount	    NUMBER,
  4  	Account_id  NUMBER)
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO bills VALUES ('PHONE COMPANY', 125, 1)
  3  INTO bills VALUES ('POWER COMPANY', 75, 1)
  4  INTO bills VALUES ('RECORD CLUB', 25, 3)
  5  INTO bills VALUES ('SOFTWARE COMPANY', 250, 5)
  6  INTO bills VALUES ('CABLE TV COMPANY', 35, 2)
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11g> CREATE INDEX BILLS_IDX ON BILLS( ACCOUNT_ID )
  2  /

Index created.

SCOTT@orcl_11g> -- query on small data set with no filter conditions does not use index:
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'BILLS')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> SELECT * FROM bills
  2  /

NAME                               AMOUNT ACCOUNT_ID
------------------------------ ---------- ----------
PHONE COMPANY                         125          1
POWER COMPANY                          75          1
RECORD CLUB                            25          3
SOFTWARE COMPANY                      250          5
CABLE TV COMPANY                       35          2


Execution Plan
----------------------------------------------------------
Plan hash value: 1359862314

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     5 |   105 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| BILLS |     5 |   105 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SCOTT@orcl_11g> -- add addition data:
SCOTT@orcl_11g> SET AUTOTRACE OFF
SCOTT@orcl_11g> INSERT INTO bills
  2  SELECT object_name, namespace, object_id
  3  FROM   all_objects
  4  WHERE  object_id > 5
  5  /

68721 rows created.

SCOTT@orcl_11g> -- query on large data set that filters on indexed column MIGHT use index:
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'BILLS')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> SELECT * FROM bills
  2  WHERE  account_id <= 5
  3  /

NAME                               AMOUNT ACCOUNT_ID
------------------------------ ---------- ----------
PHONE COMPANY                         125          1
POWER COMPANY                          75          1
CABLE TV COMPANY                       35          2
RECORD CLUB                            25          3
SOFTWARE COMPANY                      250          5


Execution Plan
----------------------------------------------------------
Plan hash value: 1207405930

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     3 |    96 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BILLS     |     3 |    96 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BILLS_IDX |     3 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ACCOUNT_ID"<=5)

SCOTT@orcl_11g> SET AUTOTRACE OFF

Re: INDEX [message #382857 is a reply to message #382846] Sun, 25 January 2009 22:33 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>I created a simple index on the Bills table (shown below) which was successful.
Consider that a column may/can contain NULL values.
An index on a NULLable column does NOT contain NULLS.
Such an index can not be used to obtain ALL rows.
Re: INDEX [message #382858 is a reply to message #382856] Sun, 25 January 2009 22:38 Go to previous message
amiravi
Messages: 3
Registered: January 2009
Junior Member
Thanks a lot everyone.

Hi Barbara,

Thanks a lot for the detailed explaination on the issue. Now I understand how index works.

Thanks & Regards,
Amit.
Previous Topic: Allow updating on few columns (3 threads merged by bb)
Next Topic: Stored Queries
Goto Forum:
  


Current Time: Fri Dec 09 10:06:19 CST 2016

Total time taken to generate the page: 0.06253 seconds