Twitter
Facebook
E-Mail
RSSOVD 결과 SQL Quary 에 Upper함수 제거 방법
Products/WebCenter Portal View Comments
A사 플젝에서 나온 사례
libOVD를 사용할때
adapter_template_usergroup1.xml
adapter_template_usergroup2=xml
를 사용하여 WCP 동기화를 구성시
SELECT DISTINCT
PT_VUSER_GROUP.EX_USER_CLASS,
PT_VUSER_GROUP.CLASS_NAME,
PT_VUSER_GROUP.LOGIN_ID
FROM EP_PORTLET.PT_VUSER_GROUP -- 뷰(INNER JOIN으로 변경 고려)
WHERE (UPPER (PT_VUSER_GROUP.EX_USER_CLASS) = UPPER ( '03')) -- 컬럼의 함수사용으로 인한 풀 스캔 이므로 좌변의 UPPER 제거 고려
ORDER BY PT_VUSER_GROUP.EX_USER_CLASS;
위와같이 결과 쿼리에 UPPER함수가 자동으로 붙어 index를 못탈경우가 생긴다
오라클 답변은 아래와 같다
Enabling (selecting) the Enable Case Insensitive Search option makes the search case insensitive for case insensitive LDAP attributes, such as uid. Oracle Virtual Directory uses UPPER in the SQL query when Enable Case Insensitive Search is enabled. If the database cannot maintain functional indexes, such as for Oracle TimesTen or MySQL databases, then you should disable the Enable Case Insensitive Search option. When the Enable Case Insensitive Search is disabled, Oracle Virtual Directory performs case sensitive searches and does not use UPPER in the SQL query. The default value for Enable Case Insensitive Search is Enable.Reference:
OVD Admin guide: http://docs.oracle.com/cd/E36909_01/admin.1111/e10046/basic_adapters.htm
useCaseInsensitiveSearch 항목을 true로 하여 WAS 재기동(admin포함) 하면 된다
default는 false 이다
<?xml version = '1.0' encoding = 'UTF-8'?>
<adapters xmlns="http://www.octetstring.com/schemas/Adapters" xmlns:ns2="http://xmlns.oracle.com/iam/management/ovd/config/plugins" version="0" schvers="303">
<ldap id="DefaultAuthenticator" version="0">
<root>ou=myrealm,dc=PortalDomain</root>
<active>true</active>
<serverType>WLS_OVD</serverType>
<routing>
<critical>true</critical>
<priority>50</priority>
<inclusionFilter/>
<exclusionFilter/>
<plugin/>
<retrieve/>
<store/>
<visible>Yes</visible>
<levels>-1</levels>
<bind>true</bind>
<bind-adapters/>
<views/>
<dnpattern/>
</routing>
<ns2:pluginChains>
<ns2:plugins>
<ns2:plugin>
<ns2:name>DMSMetrics</ns2:name>
<ns2:class>oracle.ods.virtualization.engine.chain.plugins.DMSMetrics.MonitorPerformance</ns2:class>
<ns2:initParams/>
</ns2:plugin>
<ns2:plugin>
<ns2:name>NestedGroupMembership</ns2:name>
<ns2:class>oracle.ods.virtualization.engine.chain.plugins.groupmembership.NestedGroupMembership</ns2:class>
<ns2:initParams>
<param name="nestedlevels" value="0" xmlns="http://xmlns.oracle.com/iam/management/ovd/config/plugins"/>
<param name="usersearchbase" value="ou=people,ou=myrealm,dc=PortalDomain" xmlns="http://xmlns.oracle.com/iam/management/ovd/config/plugins"/>
<param name="groupsearchbase" value="ou=groups,ou=myrealm,dc=PortalDomain" xmlns="http://xmlns.oracle.com/iam/management/ovd/config/plugins"/>
<param name="groupsearchfilter" value="(objectclass=groupOfUniqueNames)" xmlns="http://xmlns.oracle.com/iam/management/ovd/config/plugins"/>
<param name="groupmemberattribute" value="uniquemember" xmlns="http://xmlns.oracle.com/iam/management/ovd/config/plugins"/>
</ns2:initParams>
</ns2:plugin>
<ns2:plugin>
<ns2:name>DynamicGroup</ns2:name>
<ns2:class>oracle.ods.virtualization.engine.chain.plugins.DynamicGroups.DynamicGroups</ns2:class>
<ns2:initParams>
<ns2:param name="globalsearch" value="false"/>
<ns2:param name="transformdn" value="true"/>
<ns2:param name="usersearchbase" value="ou=myrealm,dc=PortalDomain"/>
</ns2:initParams>
</ns2:plugin>
</ns2:plugins>
<ns2:default>
<ns2:plugin name="DMSMetrics"/>
<ns2:plugin name="NestedGroupMembership"/>
<ns2:plugin name="DynamicGroup"/>
</ns2:default>
<ns2:get/>
<ns2:add/>
<ns2:modify/>
<ns2:delete/>
<ns2:bind/>
<ns2:rename/>
</ns2:pluginChains>
<hosts>
<host port="-1" percentage="100" readonly="false">%HOST%</host>
</hosts>
<remoteBase>ou=myrealm,dc=PortalDomain</remoteBase>
<secure>false</secure>
<failover>true</failover>
<passCredentials>Always</passCredentials>
<binddn>%DN%</binddn>
<bindpass>%PASSWORD%</bindpass>
<dnAttributeList>
<attribute>uniquemember</attribute>
<attribute>manager</attribute>
<attribute>secretary</attribute>
<attribute>owner</attribute>
<attribute>wlsmemberof</attribute>
</dnAttributeList>
<pageSize>0</pageSize>
<referals>false</referals>
<heartBeatInterval>60</heartBeatInterval>
<timeout>120000</timeout>
<maxPoolSize>100</maxPoolSize>
<maxPoolWait>1000</maxPoolWait>
<maxPoolChecks>10</maxPoolChecks>
<quickFail>false</quickFail>
<escapeSlashes>true</escapeSlashes>
<kerberos>false</kerberos>
<useDNS>No</useDNS>
<ssl>
<protocols/>
<cipherSuites/>
</ssl>
<socketOptions>
<reuseAddress>false</reuseAddress>
<keepAlive>false</keepAlive>
<tcpNoDelay>true</tcpNoDelay>
<readTimeout>180000</readTimeout>
</socketOptions>
</ldap>
<dataBase id="userGroupAdapter1" version="0">
<root>cn=users,dc=aig,dc=co,dc=kr</root>
<active>true</active>
<serverType>directoryType</serverType>
<routing>
<critical>true</critical>
<priority>50</priority>
<inclusionFilter/>
<exclusionFilter/>
<plugin/>
<retrieve/>
<store/>
<visible>Yes</visible>
<levels>-1</levels>
<bind>true</bind>
<bind-adapters/>
<views/>
<dnpattern/>
</routing>
<pluginChains xmlns="http://xmlns.oracle.com/iam/management/ovd/config/plugins">
<plugins>
<plugin>
<name>DBGUID</name>
<class>oracle.ods.virtualization.engine.chain.plugins.dbguid.DBGuidPlugin</class>
<initParams>
<param name="guidAttribute" value="orclguid"/>
</initParams>
</plugin>
</plugins>
<default>
<plugin name="DBGUID"/>
</default>
<add/>
<bind/>
<delete/>
<get/>
<modify/>
<rename/>
</pluginChains>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>datasource://jdbc/portletPrefs</url>
<user>%USER%</user>
<password>%PASSWORD%</password>
<ignoreObjectClassOnModify>false</ignoreObjectClassOnModify>
<includeInheritedObjectClasses>true</includeInheritedObjectClasses>
<maxConnections>20</maxConnections>
<mapping>
<joins/>
<objectClass name="person" rdn="cn">
<attribute ldap="cn" table="PT_VUSERS" field="LOGIN_ID" type=""/>
<attribute ldap="uid" table="PT_VUSERS" field="LOGIN_ID" type=""/>
<attribute ldap="usernameattr" table="PT_VUSERS" field="NAME" type=""/>
<attribute ldap="loginid" table="PT_VUSERS" field="LOGIN_ID" type=""/>
<attribute ldap="description" table="PT_VUSERS" field="NAME" type=""/>
<attribute ldap="orclguid" table="PT_VUSERS" field="LOGIN_ID" type=""/>
<attribute ldap="pt_id" table="PT_VUSERS" field="LOGIN_ID" type=""/>
<attribute ldap="pt_name" table="PT_VUSERS" field="NAME" type=""/>
<attribute ldap="pt_deptid" table="PT_VUSERS" field="DEPT_CD" type=""/>
<attribute ldap="pt_deptname" table="PT_VUSERS" field="DEPT_NM" type=""/>
<attribute ldap="pt_userclass" table="PT_VUSERS" field="EX_USER_CLASS" type=""/>
</objectClass>
</mapping>
<useCaseInsensitiveSearch>true</useCaseInsensitiveSearch>
<connectionWaitTimeout>10</connectionWaitTimeout>
<oracleNetConnectTimeout>0</oracleNetConnectTimeout>
<validateConnection>false</validateConnection>
</dataBase>
<dataBase id="userGroupAdapter2" version="0">
<root>cn=users,dc=aig,dc=co,dc=kr</root>
<active>true</active>
<serverType>directoryType</serverType>
<routing>
<critical>true</critical>
<priority>50</priority>
<inclusionFilter/>
<exclusionFilter/>
<plugin/>
<retrieve/>
<store/>
<visible>Yes</visible>
<levels>-1</levels>
<bind>true</bind>
<bind-adapters/>
<views/>
<dnpattern/>
</routing>
<pluginChains xmlns="http://xmlns.oracle.com/iam/management/ovd/config/plugins">
<plugins>
<plugin>
<name>VirtualAttribute</name>
<class>oracle.ods.virtualization.engine.chain.plugins.virtualattr.VirtualAttributePlugin</class>
<initParams>
<param name="ReplaceAttribute" value="uniquemember={cn=%uniquemember%,cn=users,dc=XXX,dc=co,dc=kr}"/>
</initParams>
</plugin>
</plugins>
<default>
<plugin name="VirtualAttribute"/>
</default>
<add/>
<bind/>
<delete/>
<get/>
<modify/>
<rename/>
</pluginChains>
<driver>oracle.jdbc.driver.OracleDriver</driver>
<url>datasource://jdbc/portletPrefs</url>
<user>%USER%</user>
<password>%PASSWORD%</password>
<ignoreObjectClassOnModify>false</ignoreObjectClassOnModify>
<includeInheritedObjectClasses>true</includeInheritedObjectClasses>
<maxConnections>20</maxConnections>
<mapping>
<joins/>
<objectClass name="groupofuniquenames" rdn="cn">
<attribute ldap="cn" table="PT_VUSER_GROUP" field="EX_USER_CLASS" type=""/>
<attribute ldap="description" table="PT_VUSER_GROUP" field="CLASS_NAME" type=""/>
<attribute ldap="uniquemember" table="PT_VUSER_GROUP" field="LOGIN_ID" type=""/>
</objectClass>
</mapping>
<useCaseInsensitiveSearch>true</useCaseInsensitiveSearch>
<connectionWaitTimeout>10</connectionWaitTimeout>
<oracleNetConnectTimeout>0</oracleNetConnectTimeout>
<validateConnection>false</validateConnection>
</dataBase>
</adapters>
'Products > WebCenter Portal' 카테고리의 다른 글
| WCP JPDKTest Page 안나오게 하기 (0) | 2016.07.29 |
|---|---|
| WCP 페이지 소스 보기에서 모듈버전 안나오게 하기 (0) | 2016.07.29 |
| webcenter 도메인에 webcenter_년도_NN_webcenter_metrics.dat 파일 생성 (0) | 2016.05.13 |
| WCP Deploy Weblogic Datasource 암호변경 후 오류 발생하면서 Start안될때 (0) | 2015.07.23 |
| WCP 설치 후 Admin이 등록되어 있지 않을시 EM 등록 (0) | 2015.03.02 |
back to top
Recent Comments