| Subject | Re: [IBO] Plans |
|---|---|
| Author | Helen Borrie |
| Post date | 2001-10-17T04:43:15Z |
At 05:07 PM 17-10-01 +1300, you wrote:
One or two comments:
1. If the optimiser is giving you the correct plan, why are you "forcing" the same plan?
2. Quoted identifiers are a total mess. If you can avoid using them, THEN DO!! Note these points:
a) the sole purpose/use/justification for quoted identifiers is to allow you to import data from another RDBMS that includes either (i) spaces in identifier names or (ii) identifier names that are keywords (reserved words) in IB/FB. Don't use quoted identifiers for any other purpose if you want your hair to keep growing.
b) identifiers that are created in quotes are case-sensitive. When you refer to them in SQL you do NOT need the quotes UNLESS they are one or other of (i) and (ii) above - otherwise, just make sure that your references to identifiers that were created with quotes are CASE-CORRECT and omit the quotes for a happy life. (For an even happier life, don't make your identifiers case-sensitive at all!!!)
c) since, even in a conversion from another RDBMS that allows "illegal" identifiers, you are going to be redefining indexes anyway, it is plain stoopid to create _new_ indexes with identifiers that force them to be case-sensitive in all references.
3. If you are using a desktop tool that enforces quoted identifiers regardless, then you would do well to drop that tool and use one that allows you to create _sane_ metadata.
fwiw
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________
>I have following statementSandeep,
>
>select bli."Bil_Number",cln."FName"
>from "Bill_Items" bli join "Bill_Main" bil
> on bli."Bil_Number" = bil."Ref_Number"
> join "Client_Main" cln
> on cln."Ref_Number" = bil."Cln_Number"
>
>and when I add plan
>
> *plan join (bil natural, cln index ("Cln_By_Number"), bli index
> ("Bli_By_Inv_Number"))*/
>
> to it i get error saying Index "Cln_By_Number" doesn't exists for
>table "Client_Main". I've tried using it without quotes (I'm using
>dialect 3).
>
>and when i execute it without plan the plan shown in Ibconsole is
>as
> PLAN JOIN (BIL NATURAL,CLN INDEX (Cln_By_Number),BLI
> INDEX (Bli_By_Inv_Number))
>
>Am I missing something?
One or two comments:
1. If the optimiser is giving you the correct plan, why are you "forcing" the same plan?
2. Quoted identifiers are a total mess. If you can avoid using them, THEN DO!! Note these points:
a) the sole purpose/use/justification for quoted identifiers is to allow you to import data from another RDBMS that includes either (i) spaces in identifier names or (ii) identifier names that are keywords (reserved words) in IB/FB. Don't use quoted identifiers for any other purpose if you want your hair to keep growing.
b) identifiers that are created in quotes are case-sensitive. When you refer to them in SQL you do NOT need the quotes UNLESS they are one or other of (i) and (ii) above - otherwise, just make sure that your references to identifiers that were created with quotes are CASE-CORRECT and omit the quotes for a happy life. (For an even happier life, don't make your identifiers case-sensitive at all!!!)
c) since, even in a conversion from another RDBMS that allows "illegal" identifiers, you are going to be redefining indexes anyway, it is plain stoopid to create _new_ indexes with identifiers that force them to be case-sensitive in all references.
3. If you are using a desktop tool that enforces quoted identifiers regardless, then you would do well to drop that tool and use one that allows you to create _sane_ metadata.
fwiw
Helen
All for Open and Open for All
InterBase Developer Initiative ยท http://www.interbase2000.org
_______________________________________________________