Optimize Oracle with full table Scan

Optimize Oracle with full table Scan

Today, development has met a very BT demand.
Find all friends of a person, query all works of all friends, and sort them in reverse chronological order to obtain several records,
Then associate the Work Comment table.
 
Works include the original singing table, Replaying table, accompaniment table, video table, blog table, and photo table,
Different types of works must be associated with different album tables and user tables ..

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------
The result is that this SQL statement is very long...
With
T1 as (select to_userid from friend_list f where f. userid = 411602438 ),
T2 (
Select 'mc 'as t, rid, createtime
From
(
Select mc. rowid rid, mc. createtime from music_cover mc, t1 where mc. userid = t1.to _ userid and mc. opus_stat> 0 order by mc. createtime desc
) Where rownum <100
Union all
Select 'mo', rid, createtime
From
(
Select mo. rowid rid, mo. createtime from music_original mo, t1 where mo. userid = t1.to _ userid and mo. opus_stat> 0 order by mo. createtime desc
) Where rownum <100

Union all
Select 'mv ', rid, createtime
From
(
Select mv. rowid rid, mv. createtime from music_video mv, t1 where mv. userid = t1.to _ userid and mv. opus_stat> 0 order by mv. createtime desc
) Where rownum <100

Union all
Select 'M', rid, createtime
From
(
Select ma. rowid rid, ma. createtime from music_accompany ma, t1 where ma. userid = t1.to _ userid and ma. opus_stat> 0 order by ma. createtime desc
) Where rownum <100

Union all
Select 'bl', rid, createtime
From
(
Select bl. rowid rid, bl. createtime from blog_list bl, t1 where bl. userid = t1.to _ userid and bl. opus_stat> 0 order by bl. createtime desc
) Where rownum <100

Union all
Select 'pl', rid, createtime
From
(
Select pl. rowid rid, pl. createtime from photo_list pl, t1 where pl. userid = t1.to _ userid and pl. opus_stat> 0 order by pl. createtime desc
) Where rownum <100
),
T3
(
Select * from
(
Select * from t2 order by createtime desc
)
Where rownum< 100
),
T4
(
Select
T3.t,
Decode (t3.t,
'Mc ', 2,
'Mo', 2,
'Mmv ', 2,
'Ma', 2,
'Pl', 4,
'Bl', 5
) Type_code,
Mc. userid | mo. userid | mv. userid | ma. userid | bl. userid | pl. userid,
Mc. file_url | mo. file_url | mv. file_url | ma. file_url | bl. file_url | pl. file_url,
Mc. opus_Name | mo. opus_Name | mv. opus_name | ma. opus_name | bl. opus_name | pl. opus_name,
Mc. opus_id | mo. opus_id | mv. opus_id | ma. opus_id | bl. opus_id | pl. opus_id,
Mc. createtime | mo. createtime | mv. createtime | ma. createtime | bl. createtime | pl. createtime,
Mv. opus_desc | mo. opus_desc | mc. opus_desc | ma. opus_desc | bl. opus_desc | pl. opus_desc,
Mv. album_id | mo. album_id | mc. album_id | ma. album_id | bl. album_id | pl. album_id,
Mv. visit_num | mo. visit_num | mc. visit_num | ma. visit_num | bl. visit_num | pl. visit_num
From t3
Left join music_cover mc on (t3.rid = mc. rowid)
Left join music_original mo on (t3.rid = mo. rowid)
Left join music_video mv on (t3.rid = mv. rowid)
Left join music_accompany ma on (t3.rid = ma. rowid)
Left join blog_list bl on (t3.rid = bl. rowid)
Left join photo_list pl on (t3.rid = pl. rowid)
)
Select/* + ordered use_nl (t4, base )*/
Base. nickname,
Decode (t4.type _ code,
2, (select al. album_name from music_album al where al. album_id = t4.album _ id ),
4, (select al. album_name from photo_album al where al. album_id = t4.album _ id ),
5, (select al. album_name from blog_album al where al. album_id = t4.album _ id)
) Album_name,
(Select count (*) from user_comment com where com. typeid = t4.type _ code and t4.opus _ id = com. to_id and status = 1) commentTotal,
T4 .*
From t4, mvbox_user.user_baseinfo base where base. userid = t4.userid;
 

Create an index to remove sorting
Create index inx_music_cover on music_cover (userid, opus_stat, operTime );
Create index inx_music_original on music_original (userid, opus_stat, operTime );
Create index inx_music_video on music_video (userid, opus_stat, operTime );
Create index inx_music_accompany on music_accompany (userid, opus_stat, operTime );
Create index inx_blog_list on blog_list (userid, opus_stat, operTime );
Create index inx_photo_list on photo_list (userid, opus_stat, operTime );
Create index inx_user_comment on user_comment (to_id, typeid, status );
 
A large user table is a partition table with million records. The execution plan is as expected, but the full table scan is used when the user table is last associated. This SQL statement is executed for about 20 s. However, the associated fields of the User table have already created an index ..

For more details, please continue to read the highlights on the next page:

1 2 Next Page