ORA App query with ODBC

[ide]

I have an sql query with in its first line an index command (bellow). Could I use it in Access query (and how?) or could I give the index command to the ODBC in Access with ado, dao? ..Because without this it's terrible, terribly slow..

10x
ide

SELECT /*+ INDEX(INV.MTL_MATERIAL_TRANSACTIONS INV.MTL_MATERIAL_TRANSACTIONS_N15 INV.MTL_MATERIAL_TRANSACTIONS_N1 INV.MTL_MATERIAL_TRANSACTIONS_N8) */
--    INV.MTL_MATERIAL_TRANSACTIONS.CREATION_DATE AS ComplDate,
    SUBSTR(INV.MTL_TRANSACTION_LOT_NUMBERS.LOT_NUMBER,1,7) AS ChNo,
    SUM(INV.MTL_TRANSACTION_LOT_NUMBERS.TRANSACTION_QUANTITY) Qty
FROM
	INV.MTL_MATERIAL_TRANSACTIONS,
	INV.MTL_SYSTEM_ITEMS,
	INV.MTL_TRANSACTION_LOT_NUMBERS,
	WIP.WIP_ENTITIES
WHERE
	INV.MTL_SYSTEM_ITEMS.ORGANIZATION_ID = 227
	AND INV.MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID = 227
	AND INV.MTL_TRANSACTION_LOT_NUMBERS.ORGANIZATION_ID = 227
	AND WIP.WIP_ENTITIES.ORGANIZATION_ID = 227
	AND INV.MTL_MATERIAL_TRANSACTIONS.CREATION_DATE BETWEEN '01-JAN-01' AND '31-JAN-01'
   	AND INV.MTL_MATERIAL_TRANSACTIONS.SUBINVENTORY_CODE BETWEEN '11H-KFEM' AND 'VESZELYES' --:SubInvHigh
	AND INV.MTL_SYSTEM_ITEMS.SEGMENT1 LIKE 'B6%'
	AND WIP.WIP_ENTITIES.WIP_ENTITY_ID(+) = INV.MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SOURCE_ID
	AND INV.MTL_MATERIAL_TRANSACTIONS.INVENTORY_ITEM_ID = INV.MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID
	AND INV.MTL_TRANSACTION_LOT_NUMBERS.TRANSACTION_ID(+) = INV.MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID
	AND INV.MTL_MATERIAL_TRANSACTIONS.TRANSACTION_TYPE_ID IN(17,44)
	AND WIP.WIP_ENTITIES.WIP_ENTITY_NAME LIKE 'F-%'
	AND INV.MTL_TRANSACTION_LOT_NUMBERS.LOT_NUMBER LIKE '01%'
GROUP BY
--    INV.MTL_MATERIAL_TRANSACTIONS.CREATION_DATE,
    SUBSTR(INV.MTL_TRANSACTION_LOT_NUMBERS.LOT_NUMBER,1,7)
ORDER BY
	ChNo
--	ComplDate

 

[DougP]

There is a "Create Index" function that you could run prior to your Select Statement.

You have to set a recordset up to use it though.
I copied this out of Access 2000 help. You of course don't need all of it.
-----------------------------------------
Sub CreateIndexX()

Dim dbsNorthwind As Database
Dim tdfEmployees As TableDef
Dim idxCountry As Index
Dim idxFirstName As Index
Dim idxLoop As Index

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot
Set tdfEmployees = dbsNorthwind!Employees

With tdfEmployees
' Create first Index object, create and append Field
' objects to the Index object, and then append the
' Index object to the Indexes collection of the
' TableDef.
Set idxCountry = .CreateIndex("CountryIndex&quot
With idxCountry
.Fields.Append .CreateField("Country&quot
.Fields.Append .CreateField("LastName&quot
.Fields.Append .CreateField("FirstName&quot
End With
.Indexes.Append idxCountry

' Create second Index object, create and append Field
' objects to the Index object, and then append the
' Index object to the Indexes collection of the
' TableDef.
Set idxFirstName = .CreateIndex
With idxFirstName
.Name = "FirstNameIndex"
.Fields.Append .CreateField("FirstName&quot
.Fields.Append .CreateField("LastName&quot
End With
.Indexes.Append idxFirstName

' Refresh collection so that you can access new Index
' objects.
.Indexes.Refresh

Debug.Print .Indexes.Count & " Indexes in " & _
.Name & " TableDef"

' Enumerate Indexes collection.
For Each idxLoop In .Indexes
Debug.Print " " & idxLoop.Name
Next idxLoop

' Print report.
CreateIndexOutput idxCountry
CreateIndexOutput idxFirstName

' Delete new Index objects because this is a
' demonstration.
.Indexes.Delete idxCountry.Name
.Indexes.Delete idxFirstName.Name
End With

dbsNorthwind.Close

End Sub

Function CreateIndexOutput(idxTemp As Index)

Dim fldLoop As Field
Dim prpLoop As Property

With idxTemp
' Enumerate Fields collection of Index object.
Debug.Print "Fields in " & .Name
For Each fldLoop In .Fields
Debug.Print " " & fldLoop.Name
Next fldLoop

' Enumerate Properties collection of Index object.
Debug.Print "Properties of " & .Name
For Each prpLoop In .Properties
Debug.Print " " & prpLoop.Name & " - " & _
IIf(prpLoop = "", "[empty]", prpLoop)
Next prpLoop
End With

End Function
----------------------- DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[DougP]

also there is a "CREATE INDEX" SQL steament as shown below

CREATE INDEX NewIndex ON Employees (HomePhone, _
Extension);

The following example creates an index on the Customers table with the CustomerID field. No two records can have the same data in the CustomerID field, and no Null values are allowed.

CREATE UNIQUE INDEX CustID ON Customers (CustomerID) _
WITH DISALLOW NULL;

To delete the index created in the preceding example, you must use the DROP statement. You can't delete the index from table Design view unless you also delete the relationship on the Customers table. The following statement deletes the new index by using the DROP statement:

DROP INDEX CustID ON Customers;
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive