indirect texturing is a way to overcome the 'planar uniformity' of the canonical texture mapper. as 'direct' texture mapping follows a fixed hyperbolic formula when addressing the texels from within screen space, it accounts strictly for (perspecitve) planar mapping. though succeptible to substantial optimisations (access pattern predicatibility, etc) this strictly limits your options for transformations/variations in the texture addressing. you can still apply mapping transformations but they don't break the mapping's planarity. with 'indirect' texturing, you pretty much overcome this, i.e. the dependency between the mapping of subsequtive texel fetches in texture space does not have to be hyperbolic anymore. so you get the possibility for transformations on an individual texel accesses level. a sort of 'randomisation' of the access, if you wish. think of it as a 'random-ish' look-up facility. something recent shader models can do naturally, but also something which could cost you dearly if your GPU is not, erm, mentally perpared for this : )
so to try to answer to your 'what effects' question - any effect which cannot be done with a hyperbolic, i.e. planar-based, access to your texture source array. like bumps on an otherwise planar surface, anisotropic lighting when light-mapping, any sort of irregular mappings. well, pleny of stuff, really.