Jump to content
jjeffman

Index performance on several milion records

Recommended Posts

Hello,

I have two tables :

Create table "million_data" (
	"MYTIMESTAMP" NUMBER,
	"TABLE2_KEY" NUMBER,
	"QUALITY" NUMBER,
	"THEVALUE" NUMBER
) 
/

Create Index "million_data_tst_key2" ON "million_data" ("MYTIMESTAMP","TABLE2_KEY")
TABLESPACE TBS_IDX
/
Create Index "million_data_key2_tst" ON "million_data" ("TABLE2_KEY","MYTIMESTAMP") 
TABLESPACE TBS_IDX
/


Create table "TABLE2" (
	"TABLE2_KEY" Number,
	"RECTITLE" Varchar2 (200),
	primary key ("TABLE2_KEY") USING INDEX TABLESPACE TBS_IDX 
) 

The  table "million_data" has around  5000000000 records.

The TABLE2 has 10000 records only.

Counting the records number takes 500 seconds;

The question is how can I improve the performance of the SQL to get data joining the two tables ?

Which is the best approach to create the indexes on million_table? Is the composite index a good idea ? If I need to perform queries limiting the range of MYTIMESTAMP between two values, having an index on just the MYTAMESTAMP column leads to a better performance ?

Tnank you very much.

Best regrads

Share this post


Link to post
Share on other sites

Hi Jeff and welcome to the forum!

First, did you run autotrace to view the execution plan?

http://www.dba-oracle.com/t_OracleAutotrace.htm

How many CPU’s do you have on your server?  Oracle parallel query is great for improving the speed of full-table scans:

http://www.dba-oracle.com/art_par_ecom.htm

Without indexes, Oracle will join the tables, but use a merge join.

http://www.dba-oracle.com/t_fastest_way_join_two_tables.htm

This  is some of my notes on improving join speed:

http://www.dba-oracle.com/t_fastest_way_join_two_tables.htm

For the whole story, check out my SQL tuning book:

http://rampant-books.com/book_1001_oracle_sql_tuning.htm

Please reply and let me know if this helped.

Good luck!

 

Share this post


Link to post
Share on other sites

Hi Burleson,

Thank you very much for answering me.

As this Oracle software is installed on my client's server and I have not access to the machine itself I do not know how many cpu's it has, but I guess it might have four at least.

But I have still a question.

Imagine I have built a query which compares the mytimestamp column to a value, the composite index on mytimestap and table2key is enough for joining the tables or it would be better if the million_data table has two diferents indexes one on each column instead of of the composite index ?

I do not know how Oracle uses the table indexes.

Thank you very much.

Best regards

Jayme Jeffman

Share this post


Link to post
Share on other sites

Hi Jayme,

From sqlplus or sql developer, you can get what you need.

You. An see your cpu_count, it is in this view:

select * from v$parameter;

You can also use autotrace to reveal the execution plan.  Read the link I sent!

As to the composite index question, see here:

http://www.dba-oracle.com/t_composite_index_multi_column_ordering.htm

Good luck!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×