To guard against this I should really have included the index_desc hint: - No, not really a little bit of plsql could help here.
loaded few more data into "log_data" table to make it big.
set feedback off
drop table accounts purge;
drop table log_data purge;
CREATE TABLE accounts(
id NUMBER unique not null,
account_name VARCHAR2(30)
);
CREATE TABLE log_data(
account_id NUMBER not null,
log_type NUMBER,
log_time TIMESTAMP,
msg varchar2(50)
);
insert into accounts
SELECT ROWNUM, dbms_random.string('L', 20)
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 5
);
commit;
alter session force parallel dml parallel 4 ;
insert /*+append */ into log_data
SELECT mod(rownum,5)+1, 2, SYSDATE-(ROWNUM), dbms_random.string('L', 20)
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 100000
) ;
commit;
begin
for x in 1..5
loop
dbms_application_info.set_client_info('x ='||x);
insert into log_data
SELECT mod(rownum,5)+1, 2, SYSDATE-(ROWNUM), msg
from log_data;
commit;
end loop;
end;
/
alter session disable parallel dml;
create index log_date on log_data (account_id, log_time);
begin
dbms_stats.gather_table_stats(user,'accounts');
dbms_stats.gather_table_stats(user,'log_data',degree=>4,
method_opt=>'for all indexed columns size 254');
end;
/
set feedback on
So for single account-id, got an index access.
rajesh@ORA11G> set autotrace traceonly explain
rajesh@ORA11G> select *
2 from (
3 select to_char(log_time,'yyyymmddhhmiss')||msg txt
4 from log_data
5 where account_id = 1
6 order by log_time desc
7 )
8 where rownum <= 5;
Execution Plan
----------------------------------------------------------
Plan hash value: 1871793034
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 170 | 8 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 6 | 204 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | LOG_DATA | 629K| 22M| 8 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN DESCENDING| LOG_DATE | 6 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
4 - access("ACCOUNT_ID"=1)
When i used to have Table un-nesting, it fails.(because T2 is deeply nested)
rajesh@ORA11G> select t1.id,t1.account_name,
2 to_date(substr(t2.column_value,1,14),'yyyymmddhhmiss') as log_date,
3 substr(column_value,15) as msg
4 from accounts t1,
5 table( cast( multiset(select *
6 from (
7 select to_char(log_time,'yyyymmddhhmiss')||msg txt
8 from log_data t2
9 where t2.account_id = t1.id
10 order by log_time desc
11 )
12 where rownum <= 5) as sys.odcivarchar2list)) ;
where t2.account_id = t1.id
*
ERROR at line 9:
ORA-00904: "T1"."ID": invalid identifier
with little bit of plsql, I am able to achieve it.
rajesh@ORA11G> create or replace function foo(p_id number)
2 return sys.odcivarchar2list
3 as
4 l_data sys.odcivarchar2list ;
5 begin
6 select txt
7 bulk collect into l_data
8 from (
9 select to_char(log_time,'yyyymmddhhmiss')||msg txt
10 from log_data
11 where account_id = p_id
12 order by log_time desc
13 )
14 where rownum <= 5 ;
15 return l_data;
16 end;
17 /
Function created.
rajesh@ORA11G>
rajesh@ORA11G> select t1.id,t1.account_name,
2 to_date(substr(column_value,1,14),'yyyymmddhhmiss') as log_date,
3 substr(column_value,1,15) as msg
4 from accounts t1,table(foo(t1.id)) ;
ID ACCOUNT_NAME LOG_DATE MSG
---------- ------------------------------ ----------- ---------------
1 zcwfnjvypviddxbrmgtk 01-JAN-2016 20160101043324h
1 zcwfnjvypviddxbrmgtk 01-JAN-2016 20160101043322h
1 zcwfnjvypviddxbrmgtk 01-JAN-2016 20160101043321v
1 zcwfnjvypviddxbrmgtk 01-JAN-2016 20160101043320u
1 zcwfnjvypviddxbrmgtk 01-JAN-2016 20160101043320h
2 hqujrgijcottwdryvpnl 05-JAN-2016 20160105043324d
2 hqujrgijcottwdryvpnl 05-JAN-2016 20160105043322u
2 hqujrgijcottwdryvpnl 05-JAN-2016 20160105043321r
2 hqujrgijcottwdryvpnl 05-JAN-2016 20160105043320p
2 hqujrgijcottwdryvpnl 05-JAN-2016 20160105043320r
3 aecgkaueodjhxjtqjfpq 04-JAN-2016 20160104043324l
3 aecgkaueodjhxjtqjfpq 04-JAN-2016 20160104043322b
3 aecgkaueodjhxjtqjfpq 04-JAN-2016 20160104043321i
3 aecgkaueodjhxjtqjfpq 04-JAN-2016 20160104043320j
3 aecgkaueodjhxjtqjfpq 04-JAN-2016 20160104043320f
4 ypsmyzcympcnkvrsrpof 03-JAN-2016 20160103043324j
4 ypsmyzcympcnkvrsrpof 03-JAN-2016 20160103043322n
4 ypsmyzcympcnkvrsrpof 03-JAN-2016 20160103043321p
4 ypsmyzcympcnkvrsrpof 03-JAN-2016 20160103043320b
4 ypsmyzcympcnkvrsrpof 03-JAN-2016 20160103043320r
5 ksrzynkgjhptffxzeffx 02-JAN-2016 20160102043324v
5 ksrzynkgjhptffxzeffx 02-JAN-2016 20160102043322v
5 ksrzynkgjhptffxzeffx 02-JAN-2016 20160102043321v
5 ksrzynkgjhptffxzeffx 02-JAN-2016 20160102043320t
5 ksrzynkgjhptffxzeffx 02-JAN-2016 20160102043320g
25 rows selected.
rajesh@ORA11G> exit
And Tkprof show's me this, back to index friendly
SELECT TXT
FROM
( SELECT TO_CHAR(LOG_TIME,'yyyymmddhhmiss')||MSG TXT FROM LOG_DATA WHERE
ACCOUNT_ID = :B1 ORDER BY LOG_TIME DESC ) WHERE ROWNUM <= 5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.01 0.00 0 40 0 25
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.01 0.00 0 40 0 25
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5 5 5 COUNT STOPKEY (cr=8 pr=0 pw=0 time=315 us)
5 5 5 VIEW (cr=8 pr=0 pw=0 time=309 us cost=8 size=204 card=6)
5 5 5 TABLE ACCESS BY INDEX ROWID LOG_DATA (cr=8 pr=0 pw=0 time=85 us cost=8 size=23936656 card=629912)
5 5 5 INDEX RANGE SCAN DESCENDING LOG_DATE (cr=3 pr=0 pw=0 time=52 us cost=3 size=0 card=6)(object id 92783)
With 12c in-place, it is purely SQL solution:
rajesh@ORA12C> select t1.id,t1.account_name,log_time,msg
2 from accounts t1 cross apply
3 ( select *
4 from (
5 select *
6 from log_data t2
7 where t2.account_id = t1.id
8 order by log_time desc
9 )
10 where rownum <= 5 )
11 /
ID ACCOUNT_NAME LOG_TIME MSG
---------- ----------------- ------------- --------------------------------------------------
1 jypsweynpwahnfgizfwm 01-JAN-16 05.00.49.000000 PM
mmqkrqptrshvzbepuwqx
.....
.....
.....
25 rows selected.
rajesh@ORA12C>
rajesh@ORA12C> select t1.id,t1.account_name,
2 to_date(substr(column_value,1,14),'yyyymmddhhmiss') as log_date,
3 substr(column_value,15) as msg
4 from accounts t1,
5 table( cast( multiset(select *
6 from (
7 select to_char(log_time,'yyyymmddhhmiss')||msg txt
8 from log_data t2
9 where t2.account_id = t1.id
10 order by log_time desc
11 )
12 where rownum <= 5) as sys.odcivarchar2list)) ;
ID ACCOUNT_NAME LOG_DATE
---------- ------------------------------ -----------
.....
.....
.....
25 rows selected.