Home » RDBMS Server » Performance Tuning » Big table joining (Oracle 10g)
Big table joining [message #597201] Tue, 01 October 2013 10:09 Go to next message
sss111ind
Messages: 502
Registered: April 2012
Location: India
Senior Member

Hi All,

Why the big tables are not using index when I am joining them.Please suggest me.
create table t1 as select * from all_objects;--47222

create table t2 as select * from all_objects;--47223

ALTER table t1 add constraint idx_t1_pk primary key (object_id);

ALTER table t2 add constraint idx_t2_pk primary key (object_id);

create index idx_t1_type on t1(object_type);

select * from t1,T2 where T1.object_type='INDEX' and t1.object_id=t2.object_id;

SQL> select * from t1,T2 where T1.object_type='INDEX' and t1.object_id=t2.object_id;

1700 rows selected.

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 1256071923

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  1700 |   425K|   231   (2)| 00:00:03 |
|*  1 |  HASH JOIN                   |             |  1700 |   425K|   231   (2)| 00:00:03 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1          |  1700 |   212K|    80   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T1_TYPE |  1700 |       |     6   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | T2          | 49217 |  6152K|   150   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - access("T1"."OBJECT_TYPE"='INDEX')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        881  consistent gets
          0  physical reads
          0  redo size
     147164  bytes sent via SQL*Net to client
       1735  bytes received via SQL*Net from client
        115  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1700  rows processed



Regards,
Nathan
Re: Big table joining [message #597202 is a reply to message #597201] Tue, 01 October 2013 10:11 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
What do you think this is?
sss111ind wrote on Tue, 01 October 2013 16:09

|*  3 |    INDEX RANGE SCAN          | IDX_T1_TYPE |  1700 |       |     6   (0)| 00:00:01 |


[Updated on: Tue, 01 October 2013 10:12]

Report message to a moderator

Re: Big table joining [message #597203 is a reply to message #597202] Tue, 01 October 2013 10:13 Go to previous messageGo to next message
sss111ind
Messages: 502
Registered: April 2012
Location: India
Senior Member

Thanks CookieMonster ,

But why it is full scan for T2 even if primary key is there. That is only concern here.

Re: Big table joining [message #597205 is a reply to message #597203] Tue, 01 October 2013 10:15 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
because oracle has decided it would be less efficient to use the pk than do a full table scan.
Since you are doing select * oracle still has to go to the actual t2 table to get the data so going back and forth from the index is just unnecessary overhead.

EDIT: typo

[Updated on: Tue, 01 October 2013 10:15]

Report message to a moderator

Re: Big table joining [message #597206 is a reply to message #597205] Tue, 01 October 2013 10:19 Go to previous message
BlackSwan
Messages: 23146
Registered: January 2009
Senior Member
>Why the big tables are not using index
how many rows must exist to have any table be labeled as "big"

some, many, most folks would NOT consider table with fewer than 50,000 rows to be "big".
Previous Topic: Question for List partition
Next Topic: Buffer Busy Wait DBMS_ALERT_INFO
Goto Forum:
  


Current Time: Thu Dec 18 16:25:45 CST 2014

Total time taken to generate the page: 0.10324 seconds