code_junkie

Improve oracle query performance without indexing

Nov 11th, 2011
73
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.42 KB | None | 0 0
  1. SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
  2. FROM items a,
  3. itempages b,
  4. keygroupdata c
  5. WHERE a.ItemType IN (112,115,189,241)
  6. AND a.ItemNum = b.ItemNum
  7. AND b.ItemNum = c.ItemNum
  8. ORDER BY a.DateStored DESC
  9.  
  10. SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
  11. FROM items a
  12. INNER JOIN itempages b ON b.ItemNum = a.ItemNum
  13. INNER JOIN keygroupdata c ON c.ItemNum = b.ItemNum
  14. WHERE a.ItemType IN (112,115,189,241)
  15. ORDER BY a.DateStored DESC
  16.  
  17. select
  18. c.claimnumber,
  19. a.itemdate,
  20. c.dtn,
  21. b.filepath
  22. from
  23. (
  24. select itemdate
  25. from items it
  26. where it.itemtype in(112,115,189,241)
  27. ) a
  28. itempages b,
  29. keygroupdata c
  30. where a.itemnum = b.itemnum
  31. and b.itemnum = c.itemnum
  32.  
  33. SELECT /*+RULE*/
  34. c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
  35. FROM
  36. items a,
  37. itempages b,
  38. keygroupdata c
  39. WHERE a.ItemType IN (112,115,189,241)
  40. AND a.ItemNum = b.ItemNum
  41. AND b.ItemNum = c.ItemNum
  42. ORDER BY a.DateStored DESC
  43.  
  44. SELECT c.ClaimNumber, a.ItemDate, c.DTN, b.FilePath
  45. FROM items a,
  46. itempages b,
  47. keygroupdata c
  48. WHERE ((a.ItemType IN ('112','115','189','241'))
  49. AND (a.ItemNum = b.ItemNum)
  50. AND (b.ItemNum = c.ItemNum))
  51. ORDER BY a.DateStored DESC
  52.  
  53. with a as (
  54. select
  55. *
  56. from
  57. items
  58. where
  59. ItemType IN (112,115,189,241)
  60. )
  61. SELECT
  62. c.ClaimNumber
  63. , a.ItemDate
  64. , c.DTN, b.FilePath
  65. FROM
  66. a,
  67. itempages b,
  68. keygroupdata c
  69. WHERE
  70. a.ItemNum = b.ItemNum
  71. AND b.ItemNum = c.ItemNum
  72. ORDER BY
  73. a.DateStored DESC
Add Comment
Please, Sign In to add comment