Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

Help :a query problem

heyang
2005-03-14
2014-01-19
  • heyang
    heyang
    2005-03-14

    Hi,
      i found a strange problem with the following:
    First i create two cache tables,
    CREATE CACHED TABLE TABLE_A{
        PK_A INT  NULL,
        B  INT NULL,
        C  INT NULL,
        PRIMARY KEY  (PK_A)
        );

    create  index i_B_TABLE_A on TABLE_A (
    B ASC
    );
    create  index i_C_TABLE_A on TABLE_A (
    C ASC
    );
    CREATE CACHED TABLE TABLE_B{
        PK_A INT  NULL,
        PK_B  INT NULL,
        D INT NULL;
        PRIMARY KEY  (PK_B)
        );
    create  index i_A_TABLE_B on TABLE_B (
    A ASC
    );

    each table has 50000 rows,then i run two querys:
    1.select * from TABLE_A INNER JOIN TABLE_B ON TABLE_A.PK_A=TABLE_B.PK_A WHERE  TABLE_B.D=20 AND TABLE_A.B=1000 ;
    2.select * from TABLE_A INNER JOIN TABLE_B ON TABLE_A.PK_A=TABLE_B.PK_A WHERE TABLE_B.D=20 AND (TABLE_A.B=1000 or TABLE_A.C=1000);

    The first query is very fast but the second query is very slow,i have create index on TABLE_A.B and TABLE_A.C,I can't understand why this query is so slow,can you help me to slove the problem,thanks!

     
    • Currently, adding a disjunction (and OR predicate) disables range-bounded index scans, so you've now got a full table scan.

      Try this:

      select * from TABLE_A INNER JOIN TABLE_B ON TABLE_A.PK_A=TABLE_B.PK_A WHERE TABLE_B.D=20 AND TABLE_A.B=1000

      union

      select * from TABLE_A INNER JOIN TABLE_B ON TABLE_A.PK_A=TABLE_B.PK_A WHERE TABLE_B.D=20 AND TABLE_A.C=1000

      I think you'll get similar performance to the first query this way.

       
    • heyang
      heyang
      2005-03-15

      Thanks ,But if i change the querys with the following:
      1.select * from TABLE_A INNER JOIN TABLE_B ON TABLE_A.PK_A=TABLE_B.PK_A WHERE TABLE_B.D=20 AND TABLE_A.B=1000 order by TABLE_A.PK_A;
      2.select * from TABLE_A INNER JOIN TABLE_B ON TABLE_A.PK_A=TABLE_B.PK_A WHERE TABLE_B.D=20 AND (TABLE_A.B=1000 or TABLE_A.C=1000) order by TABLE_A.PK_A;
      So i can't use union,how to solve the problem.

       
    • The standard SQL way to do this is an order by list that states the ordinal position of the order by column(s)

      Like:

      select * from TABLE_A INNER JOIN TABLE_B ON TABLE_A.PK_A=TABLE_B.PK_A WHERE TABLE_B.D=20 AND TABLE_A.B=1000

      union

      select * from TABLE_A INNER JOIN TABLE_B ON TABLE_A.PK_A=TABLE_B.PK_A WHERE TABLE_B.D=20 AND TABLE_A.C=1000

      order by <1-based-offset-of-PK_A>-in-result-column-list>

       
    • heyang
      heyang
      2005-03-15

      I see,I can solve the problem now,thanks boucherb!