Rule of Thumb
I say, it depends. It depends on who is applying the rule of thumb. It depends on how knowledgeable and experienced they are. ROT in the hands of someone with experience, with knowledge, with lots of background - very useful. They understand when and where the ROT applies.
ROT in the hands of a novice, or someone not in the "know" - as the robot in my favorite television show growing used to say "Danger, Will Robinson! Danger". You should read that Wikipedia definition there - it holds the answer to ROT in the hands of a novice:
the catch phrase is a facetious way one person tells another that he or she is about to make a stupid mistake; that there's a factor he or she overlooked which ought to be taken into account.
I think that sums it up nicely, there is a factor they have overlooked which ought to be taken into account.
ROT: Use bind variables. Almost always true, unless
- You are doing a data warehouse.
- You are running a once a month batch report.
- You are using static SQL in PL/SQL (it is impossible to not bind correctly when using static SQL in PL/SQL - use literals to your hearts content and don't be afraid!).
ROT: Find full table scans, they are evil. Not even almost always true, but fully believed anyway.
I read your advise: FTS not always evil. But if it is FTS on Very large tables, then developers demand it to be avoided: I have the following query which takes around 16 seconds if the 'mrs' table has above 25000 records.MRS has indexes on mr_seqno, mr_number, mr_template.The only thing I could say was "if that query is using a SINGLE index, something is seriously wrong - seriously totally wrong". The query was - it had one full scan and tons of nested loops (they were using the RBO). No matter what they tried, one full scan and since it was taking "long", it must be the full scan. It was more likely the LACK of full scans on the remaining tables.
FROM bps_users EXP,
WHERE rv_c.vtyv_vtyp_type(+) = 'MR REVISION CONDITION'
AND rv_c.vtyv_value(+) = mrv_complete_cancelflag
AND mrs.mr_seqno = mr_revisions.mrv_mr_seqno
AND expeditor.mrpa_mr_seqno(+) = mr_seqno
AND mr_revisions.mrv_revno = mrs.mr_lastrevno
AND EXP.busr_id(+) = expeditor.mrpa_busr_id ;
ROT: Partition a table when it exceeds N rows. Not even close to being partially true, but widely believed. You partition tables based on how you use them. You might partition a 1,000,000 row table, you might not partition a 100,000,000 row table. (how big is a row anyway?).
And there are tons of other ROTs out there. In the hands of someone that has an understanding of how the database/feature works - ROT might well be OK. I admit, I use them myself - but it is personal ROT (really, it is knowledge at that point) and I use it in conjunction with other facts. For every "rule of thumb" out there - there are hundreds of cases where (going for three again)
- It applies (it is a positive thing)
- It does not apply (it is a negative when applied in these cases)
- It neither applies not does not apply (it is totally neutral, neither good nor evil, but at best it is a distraction)
- Be good
- Be bad
- Be neither good nor bad