How to intervene the explain plan !

Sometimes , the explain plan of Oracle is not accurate ,  the way we change the explain plan is called hint, if you randomly write something inside /*asas*/ , well this is just a annotation . Hint will not work at all . if the table has a alias, you must use the alias to hint eg :  (index(t))

Select * from v$sql_hint; // check all the hints

/* + leading(t2) */       // visit t2 firstly
/* + use_nl(t1) */        // t1 is visited lastly after jpin
/* + index(id,object_id) */    // run index, but the index can't be null

Insert all into table1 into table2 select * from t // insert into multiple tables

// write pagination in this way

select * from (select t.*, rownum as rn from t t where rownum <=10) a where a.rn >= 1 

Update t set object_name = 'abc' where object_id = 8 and t.rowid='agsjgdgsgdhs'// with rowid, it will be very fast

Select /* + result_cache */ count(*) from t //cache results will increase the efficiency very much, it directly read from share pool，if query twice or triple times , logic read is 0

Create materialized view mv_count build immediate refresh on commit enable query rewrite as select count(*) from t; // create materilised view , the speed is faster

select count(*) from t; // this is super fast