hi,
I am querying the table which has about 300 million records takes about 3 seconds to get the results. Table has 5 columns with total of 150bytes/record
I am using JDBC prepared statement to query the table and query is on the primary key using IN clause with 500 distinct unique primary keys.
I really appreciate your help if you could tell me whether query response time can be reduced by tuning query or table .
Queryselect TN, CODE ,CREATED_DATE ,CREATED_BY FROM TN_DATA WHERE TN IN (:TN1,:TN2,........:TN500);
Thanks.
James
I think you probably need to trace that to see where the time is being lost. Try it after enabling a call to:
dbms_monitor.SESSION_TRACE_ENABLE(waits=>true)
and then examine the resulting trace file.
I did a little demo on my laptop (with 100mill rows), both as literals and binds and the results are much snappier. Whilst that is not definitive proof, it does suggest that you should be seeing much better results.
SQL> create table T ( pk int, data char(140));
Table created.
SQL>
SQL> insert /*+ APPEND */ into T
2 select rownum, 'x'
3 from
4 ( select 1 from dual connect by level <= 10000 ),
5 ( select 1 from dual connect by level <= 10000 )
6 /
100000000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL>
SQL> create unique index IX on T ( pk );
Index created.
SQL>
SQL> set serverout on
SQL> declare
2 l_sql varchar2(10000) := 'select count(data) from t where pk in (';
3 l_result int;
4 begin
5 for i in (
6 select trunc(dbms_random.value(1,100000000)) rand_pk
7 from dual
8 connect by level <= 500 )
9 loop
10 l_sql := l_sql ||i.rand_pk||',';
11 end loop;
12 l_sql := rtrim(l_sql,',')||')';
13
14 dbms_output.put_line(systimestamp);
15 execute immediate l_sql into l_result;
16 dbms_output.put_line(systimestamp);
17
18 end;
19 /
16-NOV-15 09.32.36.871000000 AM +08:00
16-NOV-15 09.32.37.164000000 AM +08:00
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> alter session set cursor_sharing = force;
Session altered.
SQL>
SQL> set serverout on
SQL> declare
2 l_sql varchar2(10000) := 'select count(data) from t where pk in (';
3 l_result int;
4 begin
5 for i in (
6 select trunc(dbms_random.value(1,100000000)) rand_pk
7 from dual
8 connect by level <= 500 )
9 loop
10 l_sql := l_sql ||i.rand_pk||',';
11 end loop;
12 l_sql := rtrim(l_sql,',')||')';
13
14 dbms_output.put_line(systimestamp);
15 execute immediate l_sql into l_result;
16 dbms_output.put_line(systimestamp);
17
18 end;
19 /
16-NOV-15 09.33.37.254000000 AM +08:00
16-NOV-15 09.33.37.488000000 AM +08:00
PL/SQL procedure successfully completed.
SQL>
SQL>