phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Siddharth Ubale <siddharth.ub...@syncoms.com>
Subject FW: /*+ NO_STAR_JOIN */ and Indexes
Date Thu, 20 Nov 2014 14:26:02 GMT
Hi Guys,

FYI,
Tables:
Customers contains : 9 million rows
Address :18 million rows
Orders : 18Million rows
Order_Details : 36 Million rows
Payterms & Shipterms each contain 1million rows ..

Thanks,
Siddharth Ubale

From: Siddharth Ubale
Sent: Thursday, November 20, 2014 7:44 PM
To: 'user@phoenix.apache.org'
Subject: /*+ NO_STAR_JOIN */ and Indexes

HI All,

Can we run a query which contains both /*+ NO_STAR_JOIN */ as well as /*+ INDEX(INDEXED_TABLE
INDEX/ in the same query ??

I am running a query like :

Select /*+ NO_STAR_JOIN  Index("Address" "Address_Index") Index("Customers" "Customer_Index")
Index("Orders" "Order_Index") Index("Order_Details" "Order_Detail_Index") */   * from "Address"
as a
    inner join "Customers" as c on a."A_C_Id" = c."C_Id"
   inner join "Orders" as o on a."A_C_Id" = o."O_C_Id"
    inner join "Order_Details" as od on od."O_Id" = o."O_Id"
  inner join "Payterms" as p on p."Payt_Id" = o."O_Pay_Terms"
                inner join "Shipterms" as s on s."Shipt_Id" = o."O_Ship_Terms"
                where c."C_Name_Id" = 'Zameer100002';


and I see no performance benefits over the above query where I don’t use the Index criteria
in the same query. Is there any other syntax that I should be using.

P.S :” Select /*+ NO_STAR_JOIN */  /*+  Index("Address" "Address_Index") Index("Customers"
"Customer_Index") Index("Orders" "Order_Index") Index("Order_Details" "Order_Detail_Index")
*/   * from "Address" as a………….” I tried this combination but I got an error. ☹




Thanks,
Siddharth Ubale,
Synchronized Communications
#43, Velankani Tech Park, Block No. II,
3rd Floor, Electronic City Phase I,
Bangalore – 560 100
Tel : +91 80 3202 4060
Web: www.syncoms.com<http://www.syncoms.com/>
[LogoNEWmohLARGE]
London|Bangalore|Orlando

we innovate, plan, execute, and transform the business​

Mime
View raw message